﻿Imports System.Data.OleDb
Imports System.Net
Imports System.IO

Public Class UserDetails
    Public Shared conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("AppointmentsConnectionString").ToString())

    Public Shared Function GetFacebookUserJSON(ByVal userid As String, ByVal access_token As String) As String
        Dim url As String = String.Format("https://graph.facebook.com/{0}?access_token={1}&fields=email,name,first_name,last_name,link", userid, access_token)
        Dim wc As New WebClient()
        Dim data As Stream = wc.OpenRead(url)
        Dim reader As New StreamReader(data)
        Dim s As String = reader.ReadToEnd()
        data.Close()
        reader.Close()
        Return s
    End Function

    Public Shared Function getUserId(ByVal userName As String) As Integer
        Dim UserId As Integer = -1
        Dim readUserIdSQL As String = "SELECT UserId FROM aspnet_Users WHERE UserName = @UserName"
        Dim readUserIdCmd As New OleDbCommand(readUserIdSQL, conn)
        conn.Open()
        readUserIdCmd.Parameters.AddWithValue("@UserName", userName)
        Dim reader As OleDbDataReader = readUserIdCmd.ExecuteReader()
        If (reader.Read()) Then
            UserId = CType(reader("UserId"), Integer)
        Else
            UserId = 1
        End If
        conn.Close()
        Return UserId
    End Function

    Public Shared Function getUserName(ByVal userArray As Array, ByVal fbId As String) As String
        Dim UserName As String = ""
        Dim sql As String = "SELECT UserName FROM UserFacebookIdJunction WHERE FacebookId = @FacebookId"
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
        conn.Open()
        cmd.Parameters.AddWithValue("@FacebookId", fbId)
        Dim dbread = cmd.ExecuteReader()
        dbread.Read()
        Try
            UserName = dbread("UserName")
        Catch ex As Exception

        End Try
        conn.Close()
        Return UserName
    End Function

    Public Shared Function getAllUsersByUserName() As Array
        Dim allUsers As New List(Of String)
        Dim sql As String = "SELECT UserName FROM aspnet_Users"
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
        conn.Open()
        Dim dbread = cmd.ExecuteReader()
        While (dbread.Read())
            allUsers.Add(dbread("UserName"))
        End While
        conn.Close()
        Return allUsers.ToArray()
    End Function

    Public Shared Function getAllUsersById() As Array
        Dim allUsers As New List(Of Integer)
        Dim sql As String = "SELECT UserId FROM aspnet_Users"
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
        conn.Open()
        Dim dbread = cmd.ExecuteReader()
        While (dbread.Read())
            allUsers.Add(dbread("UserId"))
        End While
        conn.Close()
        Return allUsers.ToArray()
    End Function

    Public Shared Function getAllUsersByFacebookId() As Array
        Dim allUsers As New List(Of String)
        Dim sql As String = "SELECT FacebookId FROM UserFacebookIdJunction"
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
        conn.Open()
        Dim dbread = cmd.ExecuteReader()
        While (dbread.Read())
            allUsers.Add(dbread("FacebookId"))
        End While
        conn.Close()
        Return allUsers.ToArray()
    End Function

    Public Shared Function doesUserExist(ByVal userArray As Array, ByVal newUser As String)
        For Each oldUser As String In userArray
            If oldUser = newUser Then
                Return True
            End If
        Next
        Return False
    End Function

    Public Shared Function getUserUniversity(ByVal userId As Integer, ByVal userRole As String) As Integer
        Dim userUniversity As Integer = -1
        If userRole = "Student" Then
            Dim StudentSql As String = "SELECT UniversityName FROM Student WHERE UserId = @UserId"
            Dim StudentCmd As OleDbCommand = New OleDbCommand(StudentSql, conn)
            StudentCmd.Parameters.AddWithValue("@UserId", userId)
            conn.Open()
            Dim StudentReader As OleDbDataReader = StudentCmd.ExecuteReader()
            If StudentReader.Read() Then
                userUniversity = StudentReader("UniversityName")
            End If
            conn.Close()
        ElseIf userRole = "Lecturer" Then
            Dim LecturerSql As String = "SELECT UniversityName FROM Lecturer WHERE UserId = @UserId"
            Dim LecturerCmd As OleDbCommand = New OleDbCommand(LecturerSql, conn)
            LecturerCmd.Parameters.AddWithValue("@UserId", userId)
            conn.Open()
            Dim LecturerReader As OleDbDataReader = LecturerCmd.ExecuteReader()
            If LecturerReader.Read() Then
                userUniversity = LecturerReader("UniversityName")
            End If
            conn.Close()
        ElseIf userRole = "Administrator" Then
            Dim AdminSql As String = "SELECT UniversityName FROM Lecturer WHERE UserId = @UserId"
            Dim AdminCmd As OleDbCommand = New OleDbCommand(AdminSql, conn)
            AdminCmd.Parameters.AddWithValue("@UserId", userId)
            conn.Open()
            Dim AdminReader As OleDbDataReader = AdminCmd.ExecuteReader()
            If AdminReader.Read() Then
                userUniversity = AdminReader("UniversityName")
            End If
            conn.Close()
        End If
        Return userUniversity
    End Function

    Public Shared Function getUserUniEmail(ByVal userId As String, ByVal userRole As String) As String
        Dim userUniEmail As String = "Nothing"
        If userRole = "Student" Then
            Dim StudentSql As String = "SELECT UniversityEmail FROM Student WHERE UserId = @UserId"
            Dim StudentCmd As OleDbCommand = New OleDbCommand(StudentSql, conn)
            StudentCmd.Parameters.AddWithValue("@UserId", userId)
            conn.Open()
            Dim StudentReader As OleDbDataReader = StudentCmd.ExecuteReader()
            If StudentReader.Read() Then
                userUniEmail = StudentReader("UniversityEmail")
            End If
            conn.Close()
        ElseIf userRole = "Lecturer" Then
            Dim LecturerSql As String = "SELECT UniversityEmail FROM Lecturer WHERE UserId = @UserId"
            Dim LecturerCmd As OleDbCommand = New OleDbCommand(LecturerSql, conn)
            LecturerCmd.Parameters.AddWithValue("@UserId", userId)
            conn.Open()
            Dim LecturerReader As OleDbDataReader = LecturerCmd.ExecuteReader()
            If LecturerReader.Read() Then
                userUniEmail = LecturerReader("UniversityEmail")
            End If
            conn.Close()
        ElseIf userRole = "Administrator" Then
            Dim AdminSql As String = "SELECT UniversityEmail FROM Administrator WHERE UserId = @UserId"
            Dim AdminCmd As OleDbCommand = New OleDbCommand(AdminSql, conn)
            AdminCmd.Parameters.AddWithValue("@UserId", userId)
            conn.Open()
            Dim AdminReader As OleDbDataReader = AdminCmd.ExecuteReader()
            If AdminReader.Read() Then
                userUniEmail = AdminReader("UniversityEmail")
            End If
            conn.Close()
        End If
        Return userUniEmail
    End Function
End Class
